1
成绩下滑调查
DSAI2202Lab 8: Composing Data Operations

学业支持团队需要一份 近期表现有所下滑的在读选课记录的名单。将每位学生与其在同一门课程中先前的表现进行比较。

明确规则

  1. 使用两个阶段： 先前 和 近期。每个阶段各有两次权重相同的考核。
  2. 在比较平均分之前， 每个 阶段都要求至少有两次非 NULL 成绩。
  3. 计算 decline_points = earlier_avg - recent_avg。
  4. 下滑至少 15 分意味着 需要复核。否则符合条件的选课记录为 无需提醒。
  5. 将 数据不足 单独处理。零是有效成绩；NULL 或缺失的记录并不等于零。

准备你的数据库

在 pgAdmin 中打开提供的 lab8_setup.sql （位于助教文件夹 DSAI2202/lab8/ 中）并运行整个文件。每当打开新连接时，请再次使用右侧的连接设置。

该设置还提供了 performance_comparison，这是一个称为视图的已保存查询。它将用于支持后续练习；它并不是存储考核成绩的额外数据表。

✍️ 计算前先检查
检查 E01、E07、E08、E09 和 E24。判断哪些在两个阶段都拥有足够的成绩。解释为什么 E24 的零比 E08 的 NULL 值提供更有力的证据。
设置模式
SET search_path TO lab8;
检查行数
SET search_path TO lab8;

SELECT 'students' AS table_name, COUNT(*) AS row_count
FROM students
UNION ALL SELECT 'courses', COUNT(*) FROM courses
UNION ALL SELECT 'enrolments', COUNT(*) FROM enrolments
UNION ALL SELECT 'assessment_results', COUNT(*) FROM assessment_results
UNION ALL SELECT 'support_units', COUNT(*) FROM support_units
ORDER BY table_name;
检查证据
SET search_path TO lab8;

SELECT e.enrolment_id, e.student_id, e.course_code,
       r.result_id, r.period, r.assessment_no, r.score
FROM enrolments AS e
JOIN assessment_results AS r
  ON r.enrolment_id = e.enrolment_id
WHERE e.enrolment_id IN ('E01', 'E07', 'E08', 'E09', 'E24')
ORDER BY e.enrolment_id, r.period, r.assessment_no;